import { Callout } from 'nextra/components'
import BadgeGroup, { UniverTypes } from '@/components/BadgeGroup'
import ReactLive from '@/components/ReactLive'
import ServerNotice from '@/components/ServerNotice'

# Univer Sheet API

<BadgeGroup values={[UniverTypes.SHEET]} value={UniverTypes.SHEET} />

## Concepts

Univer table-related concepts are designed to be as consistent as possible with Excel.

## Workbook

A workbook contains multiple worksheets and can be thought of as an Excel file.

The `unitId` can be used as a unique identifier for the workbook.

### Create a Workbook

The `univer.createUnit(UniverInstanceType.UNIVER_SHEET, {})` method creates and returns the `Workbook` object.

export const code = `const univer = new Univer({
  theme: defaultTheme,
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: enUS,
  },
});

univer.registerPlugin(UniverRenderEnginePlugin);
univer.registerPlugin(UniverFormulaEnginePlugin);
univer.registerPlugin(UniverUIPlugin, {
  container: 'app',
});

univer.registerPlugin(UniverDocsPlugin, {
  hasScroll: false,
});
univer.registerPlugin(UniverDocsUIPlugin);

univer.registerPlugin(UniverSheetsPlugin);
univer.registerPlugin(UniverSheetsUIPlugin);
univer.registerPlugin(UniverSheetsFormulaPlugin);

// Passing in an empty object will automatically initialize the workbook
univer.createUnit(UniverInstanceType.UNIVER_SHEET, {});`

<ReactLive code={code}  />

### Getting Workbook Data

```typescript
const univerAPI = FUniver.newAPI(univer);
const activeWorkbook = univerAPI.getActiveWorkbook()
const saveData = activeWorkbook.getSnapshot();
```

### Unload Workbook

When you no longer need a `Workbook`, you can call the `disposeUnit` method on the API instance to unload it.

```typescript
univerAPI.disposeUnit('your-sheet-id')
```

## Worksheet

Worksheets store table data, worksheets belong to the workbook.

A workbook can contain multiple worksheets, and the names of worksheets in the same workbook cannot be duplicated.

The `subUnitId` can be used to uniquely identify a sheet in a workbook.

### Get Worksheets

Get all sheets in a sheet
```typescript
const activeWorkbook = univerAPI.getActiveWorkbook();
const sheets = activeWorkbook.getSnapshot().sheets;
```

Get Active Worksheet

```typescript
const activeWorkbook = univerAPI.getActiveWorkbook();
const activeSheet = activeWorkbook.getActiveSheet();
```

### Get Worksheet Data

```typescript
const activeWorkbook = univerAPI.getActiveWorkbook();
const snapshot = activeWorkbook.getSnapshot()
const sheet1 = Object.values(snapshot.sheets).find((sheet) => {
  return sheet.name === 'Sheet1'
})
```

### Create a Worksheet

When creating a workbook, if no parameters are passed, a worksheet will be automatically created.

The following example shows how to create a worksheet using the `Workbook.create` method.

export const code2 = `const univer = new Univer({
  theme: defaultTheme,
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: enUS,
  },
});

univer.registerPlugin(UniverRenderEnginePlugin);
univer.registerPlugin(UniverFormulaEnginePlugin);
univer.registerPlugin(UniverUIPlugin, {
  container: 'app',
});

univer.registerPlugin(UniverDocsPlugin, {
  hasScroll: false,
});
univer.registerPlugin(UniverDocsUIPlugin);

univer.registerPlugin(UniverSheetsPlugin);
univer.registerPlugin(UniverSheetsUIPlugin);
univer.registerPlugin(UniverSheetsFormulaPlugin);

// Passing in an empty object will automatically initialize the workbook
univer.createUnit(UniverInstanceType.UNIVER_SHEET, {})

const univerAPI = FUniver.newAPI(univer);
//const activeWorkbook = univerAPI.getActiveWorkbook();

// Create a new sheet that named 'Sheet2' with 10 rows and 10 columns
//const sheet = activeWorkbook.create('Sheet2', 10, 10)`

<ReactLive code={code2}  />

### Remove Worksheet

Remove Worksheet by worksheet id

```typescript
const sheetId = 'SheetId';
univerAPI.executeCommand('sheet.command.remove-sheet', { subUnitId: sheetId });
```

### Activate Worksheet

To activate a worksheet, you need to know the workbook id and the sheet id.

```typescript
const workbookId = 'WorkbookId';
const sheetId = 'SheetId';
univerAPI.executeCommand('sheet.command.active-sheet', { unitId: workbookId, subUnitId: sheetId });
```

## Cell

Cell data is stored in the worksheet as a two-dimensional Map, with the first and second indexes representing the row number and column number respectively.

The following is a typical cell object:

```typescript
{
  v: 'Hello, Univer',
  s: 'styleId',
  t: CellValueType.STRING
}
```

For detailed field descriptions, please refer to [Configure Cell Data](/guides/sheet/getting-started/cell-data).

<Callout type="info" emoji="ℹ️">
  Cell operations can be regarded as operations on a range of rows and columns with a height of 1 and a width of 1. For the operation range, please refer to [Range](/guides/sheet/facade/sheet-api#range).
</Callout>

<Callout type="info" emoji="ℹ️">
  The Univer API plugin will also store the extended cell attributes in the `resources` property of the `Workbook`, please refer to [Plugin Custom Model](/guides/customization/model/).
</Callout>

### Cell PointerMove

The `onCellPointerMove` event is fired when a pointer changes coordinates.
```typescript
univerAPI.getSheetHooks().onCellPointerMove((cell) => {
  // Get the cell currently pointed to by the mouse
  console.log(cell);
})
```

### Cell PointerOver

The `onCellPointerOver` event is fired when a pointer is moved into a cell's hit test boundaries.
```typescript
univerAPI.getSheetHooks().onCellPointerOver((cell) => {
  // Get the cell currently pointed to by the mouse
  console.log(cell);
})
```

### Cell DragOver

The `onCellDragOver` event is fired when an element or text selection is being dragged into a cell's hit test boundaries.
```typescript
univerAPI.getSheetHooks().onCellDragOver((cell) => {
  // Get the cell currently pointed to by the mouse
  console.log(cell);
})
```

### Cell Drop

The `onCellDrop` event is fired when an element or text selection is being dropped on the cell.
```typescript
univerAPI.getSheetHooks().onCellDrop((cell) => {
  // Get the cell currently pointed to by the mouse
  console.log(cell);
})
```

### Cell Enters Editing

```typescript
univerAPI.onCommandExecuted((command) => {
  if(command.id === 'sheet.operation.set-cell-edit-visible' && command.params.visible){
    console.log('Cell edit visible')
  }
})
```

### Cell Exits Editing

```typescript
univerAPI.onCommandExecuted((command) => {
  if(command.id === 'sheet.operation.set-cell-edit-visible' && !command.params.visible){
    console.log('Cell edit invisible')
  }
})
```

## Range

A range refers to a rectangular area in a worksheet, which is determined by the starting row number, starting column number, length, and width, or ending row number, ending column number.

### Create a Range

To get a range you need to know the starting row number, starting column number, length and width.

Create a range of A1 cells:

```typescript
const activeSheet = univerAPI.getActiveWorkbook().getActiveSheet();
const range = activeSheet.getRange(0, 0, 1, 1);
```

Creates a range of A1:B2:

```typescript
const activeSheet = univerAPI.getActiveWorkbook().getActiveSheet();
const range = activeSheet.getRange(0, 0, 2, 2);
```

### Get Range Data

Get the value of the first cell in the range

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
const value = range.getValue();
```

Get all cell values in the range

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
range.forEach((cell, row, column) => {
  console.log(cell.getValue());
});
```

Get all formulas in the range

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
console.log(range.getFormulas());
```

### Set Range Value

#### Set a single value

If a value or cell object is passed in, all cells in the range will be overwritten. If it starts with `=`, it will be interpreted as a formula.

For example, to set the value of A1:B2 to `Hello, Univer`:
```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
range.setValue('Hello, Univer');
```

Set the value of A1+B1 to the formula:
```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
range.setValue('=A1+B1');
```

Set the value of A1:B2 to the cell object:
```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
range.setValue({
  v: 'Hello, Univer',
  custom: {
      key: 'value',
  },
});
```

#### Set multiple values with an array

The length and width of the array must match the length and width of the range.

You can pass in cell values or cell objects.

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
range.setValues([
  ['A1', 'B1'],
  ['A2', 'B2'],
]);

range.setValues([
  [{ v: 'A1' }, { v: 'B1' }],
  [{ v: 'A2' }, { v: 'B2' }],
]);
```

#### Set multiple values with an object

If an object is passed in, the primary index of the object represents the row number, and the secondary index represents the column number, and the length and width of the range do not need to match.

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
range.setValues({
  0: {
    0: 'A1',
    1: 'B1',
  },
  1: {
    0: 'A2',
    1: 'B2',
  },
});
```

### Get Range Style

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
const style = range.getCellStyleData();
```

### Set Range Style

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
range
  .setFontWeight('bold')
  .setFontLine('underline')
  .setFontFamily('Arial')
  .setFontSize(24)
  .setFontColor('red');
```

### Clear Range Style

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
range
  .setFontWeight(null)
  .setFontLine(null)
  .setFontFamily(null)
  .setFontSize(null)
  .setFontColor(null);
```

### Whether the range is merged

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
const isMerged = range.isMerged();
```

### Get the coordinates of the range

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
const rect = range.getCellRect(); // width、heigh、left、right、top、bottom、x、y
```

### Get the merge information and coordinates of the range at the same time

```typescript
const range = activeSheet.getRange(0, 0, 2, 2);
const cell = range.getCell();
```

## Selection

Univer Sheets support multiple constituencies, so a constituency is an array of ranges, and you can manipulate the constituency data through the range API.

We also provide APIs to get the current selection, set the selection, and listen for changes to the selection.

### Get Active Selection

```typescript
const activeSheet = univerAPI.getActiveWorkbook().getActiveSheet();
const selection = activeSheet.getSelection();
const range = selection.getActiveRange();
```

### Set Selection

Set A1:B2 as the selection

```typescript
const sheetId = 'SheetId';
univerAPI.executeCommand('sheet.operation.set-selections', {
  selections: [{
    range: {
      startRow: 0,
      startColumn: 0,
      endRow: 1,
      endColumn: 1,
      rangeType: 0,
    },
  }],
  subUnitId,
  unitId: activeWorkbook.getId(),
  type: 2,
})
```

### Listen for Selection Changes

```typescript
const activeWorkbook = univerAPI.getActiveWorkbook();
activeWorkbook.onSelectionChange((selection) => {
  console.log(selection);
});
```

## Server-Related Features

<ServerNotice />

### Import XLSX

For importing, you need to use the [Server Version of the Facade API](/guides/sheet/facade/facade#use-with-the-univer-server).

#### Import XLSX and Get `unitId`

In a collaborative environment, each workbook has a unique `unitId`. Use the API `importXLSXToUnitId` to pass in the `file` parameter and return `unitId`, which can be used to access the workbook. The `file` parameter can be a File object or a URL to a remote file.

```typescript
univerAPI.importXLSXToUnitId(file).then((id)=>{
  console.log(id)
})
```

#### Import XLSX and Get Workbook Data

When using the import XLSX capability separately in a non-collaborative environment, you can use the API `importXLSXToSnapshot` to return the workbook data in the IWorkbookData format.

```typescript
univerAPI.importXLSXToSnapshot(file).then((data)=>{
  console.log(data)
})
```

### Export XLSX

For exporting, you need to use the [Server Version of the Facade API](/guides/sheet/facade/facade#use-with-the-univer-server).

#### Export XLSX via `unitId`

Use the API `exportXLSXByUnitId` to pass in the `unitId` parameter and return a File object.

```typescript
univerAPI.exportXLSXByUnitId(unitId).then((file)=>{
  console.log(file)
})
```

### Export XLSX via Workbook Data

Use the API `exportXLSXBySnapshot` to pass in the table data in the `IWorkbookData` format and return a File object.

You can refer to [Getting Workbook Data](/guides/sheet/facade/sheet-api#getting-workbook-data) to get the data in the `IWorkbookData` format.

```typescript
univerAPI.exportXLSXBySnapshot(snapshot).then((file)=>{
  console.log(file)
})
```

## Reference

Please refer to the following API documentation for more information:

- [Facade API](/typedoc/@univerjs/facade/README)
